﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class RepaymentHandler
    {
        public static int AddRepayment(string Repayment, string Freq, string Method1, string NextPaymentDate, string TotalPaid, string ContractualMaturity, string ProjectedMaturity, string Residual, string Balance, string DailyRate, string LoanID)
        {
            int RepaymentID = 0;
            try
            {
                if (NextPaymentDate == "")
                    NextPaymentDate = null;
                if (ContractualMaturity == "")
                    ContractualMaturity = null;
                if (ProjectedMaturity == "")
                    ProjectedMaturity = null;

                var para = new[] {
                DBhelper.SetParameter("@Repayment", Repayment),
                DBhelper.SetParameter("@Freq", Freq),
                DBhelper.SetParameter("@Method", Method1),
                DBhelper.SetParameter("@NextPaymentDate", NextPaymentDate),
                DBhelper.SetParameter("@TotalPaid", TotalPaid),
                DBhelper.SetParameter("@ContractualMaturity", ContractualMaturity),
                DBhelper.SetParameter("@ProjectedMaturity", ProjectedMaturity),
                DBhelper.SetParameter("@Residual", Residual),
                DBhelper.SetParameter("@Balance", Balance),
                DBhelper.SetParameter("@DailyRate", DailyRate),
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlInertRepaymentQuety = "insert into am_repayments(Repayment, Freq, Method, NextPaymentDate, TotalPaid, ContractualMaturity, ProjectedMaturity, Residual, Balance, DailyRate, LoanID) values(@Repayment,@Freq,@Method,STR_TO_DATE(@NextPaymentDate,'%m/%d/%Y'),@TotalPaid,STR_TO_DATE(@ContractualMaturity,'%m/%d/%Y'),STR_TO_DATE(@ProjectedMaturity,'%m/%d/%Y'),@Residual,@Balance,@DailyRate,@LoanID);SELECT RepaymentID FROM `am_repayments` order by  RepaymentID desc limit 1";
                DataSet dtdata1 = DBhelper.ExecuteDataset(sqlInertRepaymentQuety, para);
                DataTable dt = dtdata1.Tables[0];
                if (dt.Rows.Count > 0)
                    RepaymentID = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return RepaymentID;
        }

        public static int UpdateRepayment(string Repayment, string Freq, string Method1, string NextPaymentDate, string TotalPaid, string ContractualMaturity, string ProjectedMaturity, string Residual, string Balance, string DailyRate, string LoanID)
        {
            int retvalu = 0;
            try
            {
                if (NextPaymentDate == "")
                    NextPaymentDate = null;
                if (ContractualMaturity == "")
                    ContractualMaturity = null;
                if (ProjectedMaturity == "")
                    ProjectedMaturity = null;

                var para = new[] {
                DBhelper.SetParameter("@Repayment", Repayment),
                DBhelper.SetParameter("@Freq", Freq),
                DBhelper.SetParameter("@Method", Method1),
                DBhelper.SetParameter("@NextPaymentDate", NextPaymentDate),
                DBhelper.SetParameter("@TotalPaid", TotalPaid),
                DBhelper.SetParameter("@ContractualMaturity", ContractualMaturity),
                DBhelper.SetParameter("@ProjectedMaturity", ProjectedMaturity),
                DBhelper.SetParameter("@Residual", Residual),
                DBhelper.SetParameter("@Balance", Balance),
                DBhelper.SetParameter("@DailyRate", DailyRate),
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlUpdateRepaymentQuety = "update am_repayments set Repayment=@Repayment,Freq=@Freq,Method=@Method,NextPaymentDate=STR_TO_DATE(@NextPaymentDate,'%m/%d/%Y'),TotalPaid=@TotalPaid,ContractualMaturity=STR_TO_DATE(@ContractualMaturity,'%m/%d/%Y'),ProjectedMaturity=STR_TO_DATE(@ProjectedMaturity,'%m/%d/%Y'),Residual=@Residual,Balance=@Balance,DailyRate=@DailyRate where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateRepaymentQuety, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int AddArrears(string NumofDishonours, string TotalArrears, string DaysOverdue, string DateofDefault, string LoanID)
        {
            int ArrearsID = 0;
            try
            {
                if (DateofDefault == "")
                    DateofDefault = null;

                var para = new[] {
                DBhelper.SetParameter("@NumofDishonours", NumofDishonours),
                DBhelper.SetParameter("@TotalArrears", TotalArrears),
                DBhelper.SetParameter("@DaysOverdue", DaysOverdue),
                DBhelper.SetParameter("@DateofDefault", DateofDefault),
                DBhelper.SetParameter("@LoanID", LoanID),
                };
                string sqlInertArrearsQuety = "insert into am_arrears(TotalArrears, DaysOverdue, DateofDefault, NumofDishonours, LoanID) values(@TotalArrears,@DaysOverdue,STR_TO_DATE(@DateofDefault,'%m/%d/%Y'),@NumofDishonours,@LoanID);SELECT ArrearsID FROM `am_arrears` order by  ArrearsID desc limit 1";
                DataSet dtdata1 = DBhelper.ExecuteDataset(sqlInertArrearsQuety, para);
                DataTable dt = dtdata1.Tables[0];
                if (dt.Rows.Count > 0)
                    ArrearsID = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ArrearsID;
        }

        public static int UpdateArrears(string NumofDishonours, string TotalArrears, string DaysOverdue, string DateofDefault, string LoanID)
        {
            int retvalu = 0;
            try
            {
                if (DateofDefault == "")
                    DateofDefault = null;

                var para = new[] {
                DBhelper.SetParameter("@NumofDishonours", NumofDishonours),
                DBhelper.SetParameter("@TotalArrears", TotalArrears),
                DBhelper.SetParameter("@DaysOverdue", DaysOverdue),
                DBhelper.SetParameter("@DateofDefault", DateofDefault),
                DBhelper.SetParameter("@LoanID", LoanID),
                };
                string sqlUpdateArrearsQuety = "update am_arrears set TotalArrears=@TotalArrears, DaysOverdue=@DaysOverdue, DateofDefault=STR_TO_DATE(@DateofDefault,'%m/%d/%Y'), NumofDishonours=@NumofDishonours where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateArrearsQuety, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int AddBankInfo(string BankName, string AccountNo, string BSB, int LoanID, string LoanType, string AccountHolder)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BankName", BankName),
                    DBhelper.SetParameter("@AccountNo", AccountNo),
                    DBhelper.SetParameter("@BSB", BSB),
                    DBhelper.SetParameter("@LoanID", LoanID), 
                    DBhelper.SetParameter("@LoanType", LoanType),
                    DBhelper.SetParameter("@AccountHolder", AccountHolder)  
                };
                string str = "insert into am_bankdetails(bankname,branchname,bsb,accname,accountno,SBI,LoanID,LoanType,HolderName) values(@BankName,null,@BSB,null,@AccountNo,null,@LoanID,@LoanType,@AccountHolder)";
                DBhelper.ExecuteNonQuery(str, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int UpdateBankInfo(string BankName, string AccountNo, string BSB, int LoanID, string LoanType, string AccountHolder)
        {
            string query = string.Empty;
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BankName", BankName),
                    DBhelper.SetParameter("@AccountNo", AccountNo),
                    DBhelper.SetParameter("@BSB", BSB),
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@AccountHolder", AccountHolder),
                    DBhelper.SetParameter("@LoanType", LoanType)
                };

                DataSet ds;
                try
                {
                    query = "select bankid from am_bankdetails where LoanID=@LoanID";
                    ds = DBhelper.ExecuteDataset(query, para);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        query = "update am_bankdetails set bankname=@BankName,bsb=@BSB,accountno=@AccountNo,HolderName=@AccountHolder where LoanID=@LoanID and LoanType=@LoanType";
                        DBhelper.ExecuteNonQuery(query, para);
                        intReturnValue = 1;
                    }
                    else
                    {
                        query = "insert into am_bankdetails(bankname,branchname,bsb,accname,accountno,SBI,LoanID,LoanType,HolderName) values(@BankName,null,@BSB,null,@AccountNo,null,@LoanID,@LoanType,@AccountHolder)";
                        DBhelper.ExecuteNonQuery(query, para);
                        intReturnValue = 1;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return intReturnValue;
        }

        public static DataTable SearchRepaymentByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlqry = "select * from am_repayments where loanid=@LoanID";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchArrearsByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlqry = "select * from am_arrears where loanid=@LoanID";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchBankDetailByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlqry = "select * from am_bankdetails where loanid=@LoanID";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static int AddPaymentSchedule(string PaymentSchedule, string Amount, string LoanID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentSchedule", PaymentSchedule),
                    DBhelper.SetParameter("@Amount", Amount),
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                DataSet ds = new DataSet();
                string sbSql = "Select * From am_paymentschedule Where date = @PaymentSchedule";
                ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "insert into am_paymentschedule(date,amount,LoanID) values(STR_TO_DATE(@PaymentSchedule,'%m/%d/%Y'),@Amount,@LoanID)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int EditPaymentSchedule(int paymentscheduleid, string PaymentSchedule, string Amount, string Balance)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentscheduleId", paymentscheduleid),
                    DBhelper.SetParameter("@PaymentSchedule", PaymentSchedule),
                    DBhelper.SetParameter("@Amount", Amount),
                    DBhelper.SetParameter("@Balance", Balance)
                };

                string sbSql = "update am_repaymentschedule set date = STR_TO_DATE(@PaymentSchedule,'%m/%d/%Y'), amount=@Amount,balance=@Balance where paymentscheduleid = @PaymentscheduleId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool DeletePaymentSchedule(int paymentscheduleid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentscheduleId", paymentscheduleid),
                };

                string sbSql = "delete from am_paymentschedule where paymentscheduleid=@PaymentscheduleId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        //public static DataTable DisplayPaymentSchedule(int LoanID)
        //{
        //    /* for   payment schedule */
        //    DateTime FirstPaymentDue, FirstPaymentDue1, DrawDownDate;
        //    string PaymentPeriod = string.Empty, sqlQuery = string.Empty, InterestMethod = string.Empty, Getalldate = string.Empty, PayNumber = string.Empty, Facility = string.Empty, RepaymentType = string.Empty, sqlQuery1 = string.Empty;
        //    decimal RepaymentAmount, LoanAmount, DailyPayment, TotalPayment, DailyInterest, TotalInterest, LowerRate, DailyRate = 0;
        //    int Days;
        //    decimal LoanTerm;

        //    //Create DataTable
        //    DataTable table = new DataTable();
        //    table.Columns.Add("LoanID", typeof(int));
        //    table.Columns.Add("date", typeof(DateTime));
        //    table.Columns.Add("day", typeof(string));
        //    table.Columns.Add("amount", typeof(decimal));
        //    table.Columns.Add("Balance", typeof(decimal));
        //    //End Create DataTable

        //    try
        //    {
        //        sqlQuery1 = "SELECT * FROM `am_paymentschedule` where LoanID=" + LoanID;
        //        var dtdata1 = DBhelper.ExecuteDataset(sqlQuery1.ToString()).Tables[0];
        //        if (dtdata1.Rows.Count > 0)
        //        {
        //            sqlQuery = "SELECT LoanID,LoanAmount,Product,Type,DrawDownDate,LoanTerm,FirstPaymentDue,PaymentPeriod,InterestMethod,LowerRate FROM `am_loan1` where LoanID=" + LoanID + " order by  LoanID desc limit 1";
        //            var dtdata = DBhelper.ExecuteDataset(sqlQuery.ToString()).Tables[0];
        //            if (dtdata.Rows.Count > 0)
        //            {
        //                Facility = dtdata.Rows[0]["Product"].ToString();
        //                RepaymentType = dtdata.Rows[0]["Type"].ToString();
        //                InterestMethod = dtdata.Rows[0]["InterestMethod"].ToString();
        //                if (dtdata.Rows[0]["LoanAmount"].ToString() != "")
        //                {
        //                    LoanAmount = Convert.ToDecimal(dtdata.Rows[0]["LoanAmount"].ToString());
        //                    FirstPaymentDue = Convert.ToDateTime(dtdata.Rows[0]["FirstPaymentDue"].ToString());
        //                    FirstPaymentDue1 = Convert.ToDateTime(dtdata.Rows[0]["FirstPaymentDue"].ToString());
        //                    DrawDownDate = Convert.ToDateTime(dtdata.Rows[0]["DrawDownDate"].ToString());
        //                    PaymentPeriod = dtdata.Rows[0]["PaymentPeriod"].ToString();
        //                    LowerRate = Convert.ToDecimal(dtdata.Rows[0]["LowerRate"].ToString());
        //                    string[] words = dtdata.Rows[0]["LoanTerm"].ToString().Split(' ');
        //                    LoanTerm = Convert.ToInt32(words[0]);
        //                    RepaymentAmount = LoanAmount / LoanTerm;

        //                    #region Simple
        //                    if (InterestMethod == "Simple")
        //                    {
        //                        if (Facility == "Unsecured Business Loan")
        //                        {
        //                            if (RepaymentType == "P And I")
        //                            {
        //                                if (PaymentPeriod == "Weekly")
        //                                {
        //                                    DailyRate = ((LowerRate / 100) / 365);
        //                                    DailyInterest = (LoanAmount * DailyRate);
        //                                    Days = Convert.ToInt32(LoanTerm * 7);
        //                                    TotalInterest = DailyInterest * Days;
        //                                    TotalPayment = LoanAmount + TotalInterest;
        //                                    DailyPayment = TotalPayment / LoanTerm;

        //                                    for (int i = 0; i <= LoanTerm; i++)
        //                                    {
        //                                        if (LoanAmount > DailyPayment)
        //                                        {
        //                                            LoanAmount = (LoanAmount - DailyPayment);
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
        //                                        }
        //                                        else
        //                                        {
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
        //                                            break;
        //                                        }
        //                                        FirstPaymentDue = FirstPaymentDue.AddDays(7);
        //                                    }
        //                                }
        //                                if (PaymentPeriod == "Fortnightly")
        //                                {
        //                                    DailyRate = ((LowerRate / 100) / 365);
        //                                    DailyInterest = (LoanAmount * DailyRate);
        //                                    Days = Convert.ToInt32(LoanTerm * 14);
        //                                    TotalInterest = DailyInterest * Days;
        //                                    TotalPayment = LoanAmount + TotalInterest;
        //                                    DailyPayment = TotalPayment / LoanTerm;

        //                                    for (int i = 0; i <= LoanTerm; i++)
        //                                    {
        //                                        if (LoanAmount > DailyPayment)
        //                                        {
        //                                            LoanAmount = (LoanAmount - DailyPayment);
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
        //                                        }
        //                                        else
        //                                        {
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
        //                                            break;
        //                                        }
        //                                        FirstPaymentDue = FirstPaymentDue.AddDays(14);
        //                                    }
        //                                }
        //                                if (PaymentPeriod == "Monthly")
        //                                {
        //                                    DailyRate = ((LowerRate / 100) / 365);
        //                                    DailyInterest = (LoanAmount * DailyRate);
        //                                    Days = Convert.ToInt32(LoanTerm * 30);
        //                                    TotalInterest = DailyInterest * Days;
        //                                    TotalPayment = LoanAmount + TotalInterest;
        //                                    DailyPayment = TotalPayment / LoanTerm;

        //                                    for (int i = 0; i <= LoanTerm; i++)
        //                                    {
        //                                        if (LoanAmount > DailyPayment)
        //                                        {
        //                                            LoanAmount = (LoanAmount - DailyPayment);
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
        //                                        }
        //                                        else
        //                                        {
        //                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
        //                                            break;
        //                                        }
        //                                        FirstPaymentDue = FirstPaymentDue.AddDays(30);
        //                                    }
        //                                }
        //                                if (PaymentPeriod == "Daily(Mon-Fri)")
        //                                {
        //                                    DailyRate = ((LowerRate / 100) / 365);
        //                                    DailyInterest = (LoanAmount * DailyRate);
        //                                    Days = Convert.ToInt32(LoanTerm * 1);
        //                                    TotalInterest = DailyInterest * Days;
        //                                    TotalPayment = LoanAmount + TotalInterest;
        //                                    DailyPayment = TotalPayment / LoanTerm;

        //                                    for (int i = 0; i <= LoanTerm; i++)
        //                                    {
        //                                        if (FirstPaymentDue.DayOfWeek.ToString() != "Saturday" && FirstPaymentDue.DayOfWeek.ToString() != "Sunday")
        //                                        {
        //                                            if (LoanAmount > DailyPayment)
        //                                            {
        //                                                LoanAmount = (LoanAmount - DailyPayment);
        //                                                table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
        //                                            }
        //                                            else
        //                                            {
        //                                                table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
        //                                                break;
        //                                            }
        //                                        }
        //                                        else
        //                                        {
        //                                            i = i - 1;
        //                                        }
        //                                        FirstPaymentDue = FirstPaymentDue.AddDays(1);
        //                                    }
        //                                }
        //                            }
        //                        }
        //                    }
        //                    #endregion
        //                }
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return table;
        //}

        public static DataTable DisplayPaymentSchedule(int LoanID)
        {
            DataTable table = null;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlqry = "select date,CAST(DAYNAME(date) AS CHAR) as day,amount from am_repaymentschedule where loanid=@LoanID order by date";
                table = DBhelper.ExecuteDataset(sqlqry,para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

    }
}
